/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package bookingsystem;

import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.net.Socket;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

/**
 *
 * @author Pawel
 */
public class ServerThread implements Runnable {
    private Socket client = null;
    ArrayList flights = new ArrayList(); 
    ResultSet rset, rowCount = null;
    Reservation reservation = null;
    ObjectOutputStream oos = null;
    ObjectInputStream ois = null;
    
    public ServerThread(Socket client) {
        this.client = client;
        try {
            oos = new ObjectOutputStream(client.getOutputStream());
            ois = new ObjectInputStream(client.getInputStream());
        } catch (IOException e){
            System.out.println(e);
        }
    }
    
    public void run() {
        try {
            Object inputObj;
            while ((inputObj = ois.readObject()) != null) {
                if (inputObj.equals("areYouAlive"))
                    sendHeartBeat();
                else if (inputObj.equals("FlightList"))
                    sendFlightList();
                else if (inputObj.equals("bookReservation"))
                    bookReservation();
                else if (inputObj.equals("updateReservation"))
                    updateReservation();
                else if (inputObj.equals("cancelReservation"))
                    cancelReservation();
                else
                    checkReservation(inputObj);
                System.out.println(inputObj);

             }
        }
        catch (ClassNotFoundException e){
            System.out.println(e);
        }
        catch (IOException e){
            System.out.println(e);
        }
    }
            
    public void bookReservation() {
        try {
            DBConnection conn = new DBConnection();
            Connection connection = conn.getConnection();
            Statement s=connection.createStatement();

            reservation = (Reservation) ois.readObject();
            Date dateNow = new Date();
            String flightDate = reservation.getDate()+" "+reservation.getTime();
            SimpleDateFormat dateformatYYYYMMDD = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            String bookingDate = dateformatYYYYMMDD.format(dateNow);
            rset = s.executeQuery("Select idreservation from reservation where idreservation like 'AA%' order by idreservation desc limit 1");
            String idReservation = "";
            int idNumber = 0;
            if (rset.next()){
                rset.beforeFirst();
                while (rset.next()){
                    idReservation = "AA";//rset.getString(1).substring(0, 2);
                    idNumber = (int) Integer.parseInt(rset.getString(1).substring(2));
                    idNumber += 1;
                    idReservation = idReservation+idNumber;
                }
            }
            else {
                idReservation = "AA1000001";
            }
            boolean status = s.execute("Insert into reservation values ('"+idReservation+"','"+reservation.getOrigin()+"','"+reservation.getDestination()+"','"+flightDate+"','"+reservation.getFlightNo()+"','"+bookingDate+"','"+reservation.getName()+"','"+reservation.getLastName()+"','"+reservation.getStatus()+"')");
            s.execute("Update flight Set booked_seats=booked_seats+1 Where idflight='"+reservation.getFlightNo()+"'");
            if (!status){
                oos.writeObject(idReservation);
                oos.flush();
            }
        }
        catch (ClassNotFoundException e){
            System.out.println(e);
        }
        catch (IOException e){
            System.out.println(e);
        }
        catch (SQLException e) { System.out.println("Conn failed: "+e);// Could not connect to the database
        }
    }
    
    public void sendHeartBeat() {
        try {
            oos.writeObject("ready");
            oos.flush();   
        } catch (IOException e){
            System.out.println(e);
        }
    }
    
    public void sendFlightList() {
        try {
        DBConnection conn = new DBConnection();
        Connection connection = conn.getConnection();
        Statement s=connection.createStatement();
        rset = s.executeQuery("Select * from flight where booked_seats < seats_capacity");

            while (rset.next()){
                int flightid = (int) Integer.parseInt(rset.getString(1));
                int seatsCapacity = (int) Integer.parseInt(rset.getString(2));
                int bookedSeats = (int) Integer.parseInt(rset.getString(3));
                String origin = rset.getString(4);
                String destination = rset.getString(5);
                String flightDate = rset.getString(6);
                String date = flightDate.substring(0, 10);
                String time = flightDate.substring(11, 16);
                flights.add(new Flight(flightid, origin,destination,date,time,seatsCapacity,bookedSeats));
            }

        oos.writeObject(flights);
        oos.flush();
        }
        catch (IOException e){
            System.out.println(e);
        }
        catch (SQLException e) { System.out.println("Conn failed: "+e);// Could not connect to the database
        }
    }
    
    public void checkReservation(Object inputObj) {
        String resId = inputObj.toString();
        String resStatus = "";
        String flightDate = "";
        String[] reservations = new String[2];
        try {
            DBConnection conn = new DBConnection();
            Connection connection = conn.getConnection();
            Statement s=connection.createStatement();
            rset = s.executeQuery("Select * from reservation where idreservation = '"+resId+"'");

            while (rset.next()){
                flightDate = rset.getString(4);
                resStatus = rset.getString(9);
            }
            reservations[0] = resStatus;
            reservations[1] = flightDate;
            oos.writeObject(reservations);
            oos.flush();
        }
        catch (IOException e){
            System.out.println(e);
        }
        catch (SQLException e) { System.out.println("Conn failed: "+e);// Could not connect to the database
        }
    }
    
    public void updateReservation() {
        try {
            String resId = "";
            String resStatus = "OK";
            String [] reservations = new String[2];
            reservations = (String[]) ois.readObject();
            resId = reservations[0];
            DBConnection conn = new DBConnection();
            Connection connection = conn.getConnection();
            Statement s=connection.createStatement();
            s.execute("Update reservation Set status='"+resStatus+"' Where idreservation='"+resId+"'");
        }
        catch (IOException e){
            System.out.println(e);
        }
        catch (ClassNotFoundException e) {
            System.out.println(e);
        }
        catch (SQLException e) { System.out.println("Conn failed: "+e);// Could not connect to the database
        }
    }
    
    public void cancelReservation() {
        try {
            String resId = "";
            String resStatus = "Cancelled";
            String [] reservations = new String[2];
            reservations = (String[]) ois.readObject();
            resId = reservations[0];
            DBConnection conn = new DBConnection();
            Connection connection = conn.getConnection();
            Statement s=connection.createStatement();
            s.execute("Update reservation Set status='"+resStatus+"' Where idreservation='"+resId+"'");
        }
        catch (IOException e){
            System.out.println(e);
        }
        catch (ClassNotFoundException e) {
            System.out.println(e);
        }
        catch (SQLException e) { System.out.println("Conn failed: "+e);// Could not connect to the database
        }
    }
}
